
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/25/2011 02:05:48
-- Generated from EDMX file: C:\Projects\dotnet\Project_repo\Code\NETMovie\NETMovie.Entities\NETMovieEntities.edmx
-- --------------------------------------------------

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_MovieCastMember_Movie]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MovieCastMember] DROP CONSTRAINT [FK_MovieCastMember_Movie];
--GO
IF OBJECT_ID(N'[dbo].[FK_MovieCastMember_CastMember]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MovieCastMember] DROP CONSTRAINT [FK_MovieCastMember_CastMember];
--GO
IF OBJECT_ID(N'[dbo].[FK_MovieMovieCategory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Movies] DROP CONSTRAINT [FK_MovieMovieCategory];
--GO
IF OBJECT_ID(N'[dbo].[FK_MovieArticle]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [FK_MovieArticle];
--GO
IF OBJECT_ID(N'[dbo].[FK_CastMemberArticle]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [FK_CastMemberArticle];
--GO
IF OBJECT_ID(N'[dbo].[FK_MovieRentalMovie]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MovieRentals] DROP CONSTRAINT [FK_MovieRentalMovie];
--GO
IF OBJECT_ID(N'[dbo].[FK_UserMovieRental]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MovieRentals] DROP CONSTRAINT [FK_UserMovieRental];
--GO
IF OBJECT_ID(N'[dbo].[FK_UserMovie_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserMovie] DROP CONSTRAINT [FK_UserMovie_User];
--GO
IF OBJECT_ID(N'[dbo].[FK_UserMovie_Movie]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserMovie] DROP CONSTRAINT [FK_UserMovie_Movie];
--GO
IF OBJECT_ID(N'[dbo].[FK_MovieUserComment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserComments] DROP CONSTRAINT [FK_MovieUserComment];
--GO
IF OBJECT_ID(N'[dbo].[FK_UserCommentUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserComments] DROP CONSTRAINT [FK_UserCommentUser];
--GO
IF OBJECT_ID(N'[dbo].[FK_TransactionMovieRental]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MovieRentals] DROP CONSTRAINT [FK_TransactionMovieRental];
--GO
IF OBJECT_ID(N'[dbo].[FK_TransactionUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Transactions] DROP CONSTRAINT [FK_TransactionUser];
--GO
IF OBJECT_ID(N'[dbo].[FK_CountryUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_CountryUser];
--GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
--GO
IF OBJECT_ID(N'[dbo].[Movies]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Movies];
--GO
IF OBJECT_ID(N'[dbo].[MovieCategories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MovieCategories];
--GO
IF OBJECT_ID(N'[dbo].[CastMembers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CastMembers];
--GO
IF OBJECT_ID(N'[dbo].[MovieRentals]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MovieRentals];
--GO
IF OBJECT_ID(N'[dbo].[Articles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Articles];
--GO
IF OBJECT_ID(N'[dbo].[UserComments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserComments];
--GO
IF OBJECT_ID(N'[dbo].[Transactions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Transactions];
--GO
IF OBJECT_ID(N'[dbo].[SystemParameters]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SystemParameters];
--GO
IF OBJECT_ID(N'[dbo].[Countries]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Countries];
--GO
IF OBJECT_ID(N'[dbo].[MovieCastMember]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MovieCastMember];
--GO
IF OBJECT_ID(N'[dbo].[UserMovie]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserMovie];
--GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserId] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NULL,
    [AddressLine1] nvarchar(max)  NOT NULL,
    [AddressLine2] nvarchar(max)  NULL,
    [City] nvarchar(max)  NULL,
    [PostalCode] nvarchar(max)  NOT NULL,
    [Country_CountryCode] nvarchar(20)  NOT NULL
);
--GO

-- Creating table 'Movies'
CREATE TABLE [dbo].[Movies] (
    [MovieId] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [ReleaseDate] datetime  NOT NULL,
    [AvailableDate] datetime  NOT NULL,
    [IsPremium] bit  NOT NULL,
    [DeleteFlg] bit  NOT NULL,
    [FileName] nvarchar(max)  NOT NULL,
    [Category_CategoryCode] nvarchar(20)  NOT NULL
);
--GO

-- Creating table 'MovieCategories'
CREATE TABLE [dbo].[MovieCategories] (
    [CategoryCode] nvarchar(20)  NOT NULL,
    [CategoryName] nvarchar(max)  NOT NULL
);
--GO

-- Creating table 'CastMembers'
CREATE TABLE [dbo].[CastMembers] (
    [CastMemberId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Role] nvarchar(max)  NOT NULL
);
--GO

-- Creating table 'MovieRentals'
CREATE TABLE [dbo].[MovieRentals] (
    [MovieRentalId] int IDENTITY(1,1) NOT NULL,
    [Amount] decimal(18,0)  NOT NULL,
    [IssueDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [ReturnDate] datetime  NULL,
    [Movie_MovieId] int  NOT NULL,
    [UserMovieRental_MovieRental_UserId] int  NOT NULL,
    [Transaction_TransactionId] int  NOT NULL
);
--GO

-- Creating table 'Articles'
CREATE TABLE [dbo].[Articles] (
    [ArticleId] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [ArticleURL] nvarchar(max)  NOT NULL,
    [SourceName] nvarchar(max)  NOT NULL,
    [SourceURL] nvarchar(max)  NOT NULL,
    [MovieArticle_Article_MovieId] int  NULL,
    [CastMemberArticle_Article_CastMemberId] int  NULL
);
--GO

-- Creating table 'UserComments'
CREATE TABLE [dbo].[UserComments] (
    [UserCommentId] int IDENTITY(1,1) NOT NULL,
    [CommentText] nvarchar(max)  NOT NULL,
    [MovieUserComment_UserComment_MovieId] int  NOT NULL,
    [User_UserId] int  NOT NULL
);
--GO

-- Creating table 'Transactions'
CREATE TABLE [dbo].[Transactions] (
    [TransactionId] int IDENTITY(1,1) NOT NULL,
    [TransactionDate] datetime  NOT NULL,
    [TransactionNo] nvarchar(max)  NOT NULL,
    [TotalAmount] decimal(18,0)  NOT NULL,
    [IsPaid] bit  NOT NULL,
    [GST] decimal(18,0)  NOT NULL,
    [CreateDate] datetime  NOT NULL,
    [User_UserId] int  NOT NULL
);
--GO

-- Creating table 'SystemParameters'
CREATE TABLE [dbo].[SystemParameters] (
    [Code] nvarchar(20)  NOT NULL,
    [Value] nvarchar(max)  NOT NULL
);
--GO

-- Creating table 'Countries'
CREATE TABLE [dbo].[Countries] (
    [CountryCode] nvarchar(20)  NOT NULL,
    [CountryName] nvarchar(max)  NOT NULL
);
--GO

-- Creating table 'MovieCastMember'
CREATE TABLE [dbo].[MovieCastMember] (
    [Movies_MovieId] int  NOT NULL,
    [CastMembers_CastMemberId] int  NOT NULL
);
--GO

-- Creating table 'UserMovie'
CREATE TABLE [dbo].[UserMovie] (
    [UserMovie_Movie_UserId] int  NOT NULL,
    [Favorites_MovieId] int  NOT NULL
);
--GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [UserId] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
--GO

-- Creating primary key on [MovieId] in table 'Movies'
ALTER TABLE [dbo].[Movies]
ADD CONSTRAINT [PK_Movies]
    PRIMARY KEY CLUSTERED ([MovieId] ASC);
--GO

-- Creating primary key on [CategoryCode] in table 'MovieCategories'
ALTER TABLE [dbo].[MovieCategories]
ADD CONSTRAINT [PK_MovieCategories]
    PRIMARY KEY CLUSTERED ([CategoryCode] ASC);
--GO

-- Creating primary key on [CastMemberId] in table 'CastMembers'
ALTER TABLE [dbo].[CastMembers]
ADD CONSTRAINT [PK_CastMembers]
    PRIMARY KEY CLUSTERED ([CastMemberId] ASC);
--GO

-- Creating primary key on [MovieRentalId] in table 'MovieRentals'
ALTER TABLE [dbo].[MovieRentals]
ADD CONSTRAINT [PK_MovieRentals]
    PRIMARY KEY CLUSTERED ([MovieRentalId] ASC);
--GO

-- Creating primary key on [ArticleId] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [PK_Articles]
    PRIMARY KEY CLUSTERED ([ArticleId] ASC);
--GO

-- Creating primary key on [UserCommentId] in table 'UserComments'
ALTER TABLE [dbo].[UserComments]
ADD CONSTRAINT [PK_UserComments]
    PRIMARY KEY CLUSTERED ([UserCommentId] ASC);
--GO

-- Creating primary key on [TransactionId] in table 'Transactions'
ALTER TABLE [dbo].[Transactions]
ADD CONSTRAINT [PK_Transactions]
    PRIMARY KEY CLUSTERED ([TransactionId] ASC);
--GO

-- Creating primary key on [Code] in table 'SystemParameters'
ALTER TABLE [dbo].[SystemParameters]
ADD CONSTRAINT [PK_SystemParameters]
    PRIMARY KEY CLUSTERED ([Code] ASC);
--GO

-- Creating primary key on [CountryCode] in table 'Countries'
ALTER TABLE [dbo].[Countries]
ADD CONSTRAINT [PK_Countries]
    PRIMARY KEY CLUSTERED ([CountryCode] ASC);
--GO

-- Creating primary key on [Movies_MovieId], [CastMembers_CastMemberId] in table 'MovieCastMember'
ALTER TABLE [dbo].[MovieCastMember]
ADD CONSTRAINT [PK_MovieCastMember]
    PRIMARY KEY NONCLUSTERED ([Movies_MovieId], [CastMembers_CastMemberId] ASC);
--GO

-- Creating primary key on [UserMovie_Movie_UserId], [Favorites_MovieId] in table 'UserMovie'
ALTER TABLE [dbo].[UserMovie]
ADD CONSTRAINT [PK_UserMovie]
    PRIMARY KEY NONCLUSTERED ([UserMovie_Movie_UserId], [Favorites_MovieId] ASC);
--GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Movies_MovieId] in table 'MovieCastMember'
ALTER TABLE [dbo].[MovieCastMember]
ADD CONSTRAINT [FK_MovieCastMember_Movie]
    FOREIGN KEY ([Movies_MovieId])
    REFERENCES [dbo].[Movies]
        ([MovieId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
--GO

-- Creating foreign key on [CastMembers_CastMemberId] in table 'MovieCastMember'
ALTER TABLE [dbo].[MovieCastMember]
ADD CONSTRAINT [FK_MovieCastMember_CastMember]
    FOREIGN KEY ([CastMembers_CastMemberId])
    REFERENCES [dbo].[CastMembers]
        ([CastMemberId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MovieCastMember_CastMember'
CREATE INDEX [IX_FK_MovieCastMember_CastMember]
ON [dbo].[MovieCastMember]
    ([CastMembers_CastMemberId]);
--GO

-- Creating foreign key on [Category_CategoryCode] in table 'Movies'
ALTER TABLE [dbo].[Movies]
ADD CONSTRAINT [FK_MovieMovieCategory]
    FOREIGN KEY ([Category_CategoryCode])
    REFERENCES [dbo].[MovieCategories]
        ([CategoryCode])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MovieMovieCategory'
CREATE INDEX [IX_FK_MovieMovieCategory]
ON [dbo].[Movies]
    ([Category_CategoryCode]);
--GO

-- Creating foreign key on [MovieArticle_Article_MovieId] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [FK_MovieArticle]
    FOREIGN KEY ([MovieArticle_Article_MovieId])
    REFERENCES [dbo].[Movies]
        ([MovieId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MovieArticle'
CREATE INDEX [IX_FK_MovieArticle]
ON [dbo].[Articles]
    ([MovieArticle_Article_MovieId]);
--GO

-- Creating foreign key on [CastMemberArticle_Article_CastMemberId] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [FK_CastMemberArticle]
    FOREIGN KEY ([CastMemberArticle_Article_CastMemberId])
    REFERENCES [dbo].[CastMembers]
        ([CastMemberId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CastMemberArticle'
CREATE INDEX [IX_FK_CastMemberArticle]
ON [dbo].[Articles]
    ([CastMemberArticle_Article_CastMemberId]);
--GO

-- Creating foreign key on [Movie_MovieId] in table 'MovieRentals'
ALTER TABLE [dbo].[MovieRentals]
ADD CONSTRAINT [FK_MovieRentalMovie]
    FOREIGN KEY ([Movie_MovieId])
    REFERENCES [dbo].[Movies]
        ([MovieId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MovieRentalMovie'
CREATE INDEX [IX_FK_MovieRentalMovie]
ON [dbo].[MovieRentals]
    ([Movie_MovieId]);
--GO

-- Creating foreign key on [UserMovieRental_MovieRental_UserId] in table 'MovieRentals'
ALTER TABLE [dbo].[MovieRentals]
ADD CONSTRAINT [FK_UserMovieRental]
    FOREIGN KEY ([UserMovieRental_MovieRental_UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserMovieRental'
CREATE INDEX [IX_FK_UserMovieRental]
ON [dbo].[MovieRentals]
    ([UserMovieRental_MovieRental_UserId]);
--GO

-- Creating foreign key on [UserMovie_Movie_UserId] in table 'UserMovie'
ALTER TABLE [dbo].[UserMovie]
ADD CONSTRAINT [FK_UserMovie_User]
    FOREIGN KEY ([UserMovie_Movie_UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
--GO

-- Creating foreign key on [Favorites_MovieId] in table 'UserMovie'
ALTER TABLE [dbo].[UserMovie]
ADD CONSTRAINT [FK_UserMovie_Movie]
    FOREIGN KEY ([Favorites_MovieId])
    REFERENCES [dbo].[Movies]
        ([MovieId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserMovie_Movie'
CREATE INDEX [IX_FK_UserMovie_Movie]
ON [dbo].[UserMovie]
    ([Favorites_MovieId]);
--GO

-- Creating foreign key on [MovieUserComment_UserComment_MovieId] in table 'UserComments'
ALTER TABLE [dbo].[UserComments]
ADD CONSTRAINT [FK_MovieUserComment]
    FOREIGN KEY ([MovieUserComment_UserComment_MovieId])
    REFERENCES [dbo].[Movies]
        ([MovieId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MovieUserComment'
CREATE INDEX [IX_FK_MovieUserComment]
ON [dbo].[UserComments]
    ([MovieUserComment_UserComment_MovieId]);
--GO

-- Creating foreign key on [User_UserId] in table 'UserComments'
ALTER TABLE [dbo].[UserComments]
ADD CONSTRAINT [FK_UserCommentUser]
    FOREIGN KEY ([User_UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserCommentUser'
CREATE INDEX [IX_FK_UserCommentUser]
ON [dbo].[UserComments]
    ([User_UserId]);
--GO

-- Creating foreign key on [User_UserId] in table 'Transactions'
ALTER TABLE [dbo].[Transactions]
ADD CONSTRAINT [FK_TransactionUser]
    FOREIGN KEY ([User_UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TransactionUser'
CREATE INDEX [IX_FK_TransactionUser]
ON [dbo].[Transactions]
    ([User_UserId]);
--GO

-- Creating foreign key on [Country_CountryCode] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_CountryUser]
    FOREIGN KEY ([Country_CountryCode])
    REFERENCES [dbo].[Countries]
        ([CountryCode])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CountryUser'
CREATE INDEX [IX_FK_CountryUser]
ON [dbo].[Users]
    ([Country_CountryCode]);
--GO

-- Creating foreign key on [Transaction_TransactionId] in table 'MovieRentals'
ALTER TABLE [dbo].[MovieRentals]
ADD CONSTRAINT [FK_MovieRentalTransaction]
    FOREIGN KEY ([Transaction_TransactionId])
    REFERENCES [dbo].[Transactions]
        ([TransactionId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MovieRentalTransaction'
CREATE INDEX [IX_FK_MovieRentalTransaction]
ON [dbo].[MovieRentals]
    ([Transaction_TransactionId]);
--GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------